﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Transactions;
public partial class WebForm_ManageCodeMapping_AddCodeMapping : System.Web.UI.UserControl
{
    //protected void Page_Init(object sender, EventArgs e)
    //{
    //    this.fvParameter.InsertItemTemplate = this.fvParameter.EditItemTemplate;
    //}

    protected void Page_Load(object sender, EventArgs e)
    {
        
    }

    public void BindFVForInsert()
    {
        fvParameter.ChangeMode(FormViewMode.Insert);
        fvParameter.DataBind();
    }

    public void BindFV(string tableName, string columnName, string value)
    {
        this.AddObjDataSource.SelectParameters["TableName"].DefaultValue = tableName;
        this.AddObjDataSource.SelectParameters["ColumnName"].DefaultValue = columnName;
        this.AddObjDataSource.SelectParameters["Value"].DefaultValue = value;

        fvParameter.ChangeMode(FormViewMode.Edit);
        fvParameter.DataBind();
    }

    protected void fvParameter_ItemUpdating(object sender, FormViewUpdateEventArgs e)
    {
        //string s1 = Session["TableName"].ToString();
        //string s2 = Session["ColumnName"].ToString();
    }

    protected void fvParameter_ItemCommand(object sender, FormViewCommandEventArgs e)
    {

    }

    protected void fvParameter_DataBound(object sender, EventArgs  e)
    {
        if (this.fvParameter.CurrentMode == FormViewMode.Insert)
        {
            this.LoadTableNames();

            //MyObj.ShowMessage((this.fvParameter.FindControl("ddlTableText") as DropDownList).SelectedValue);
            this.ObjectDataSource2.SelectParameters["CodeHeaderID"].DefaultValue = (this.fvParameter.FindControl("ddlTableText") as DropDownList).SelectedValue;
            (this.fvParameter.FindControl("ddlColumnName") as DropDownList).DataSourceID = this.ObjectDataSource2.ID;            
        }
        
    }

    protected void AddObjDataSource_Updating(object sender, ObjectDataSourceMethodEventArgs e)
    {
        //string tablename = Session["TableName"].ToString();
        //string columnname = Session["ColumnName"].ToString();
        string value = Session["Value"].ToString();
        string text = ((TextBox)fvParameter.FindControl("Edit_txtText")).Text; 
    }

    //編輯時按下儲存
    protected void btnSave_Click(object sender, EventArgs e)
    {
        //string v = ((RequiredFieldValidator)fvParameter.FindControl("rfvText1")).IsValid.ToString();
        
        string tableName = ((Label)fvParameter.FindControl("Edit_lblTableName")).Text;
        string columnName = ((Label)fvParameter.FindControl("Edit_lblColumnName")).Text;
        string value = ((Label)fvParameter.FindControl("Edit_lblValue")).Text; ;
        string text = ((TextBox)fvParameter.FindControl("Edit_txtText")).Text;
        string MOIValue = ((TextBox)fvParameter.FindControl("Edit_txtMOIValue")).Text;
        UpdateCodeMapping(tableName, columnName, value, text, MOIValue);

        if (SelectCodeItem(tableName, columnName).Rows.Count > 0)
        {
            string ItemCodeHeaderID = SelectCodeItem(tableName, columnName).Rows[0]["ItemCodeHeaderID"].ToString();
            string ItemColumnName = SelectCodeItem(tableName, columnName).Rows[0]["ItemColumnName"].ToString();
            string ItemTableName = SelectCodeItem(tableName, columnName).Rows[0]["ItemTableName"].ToString();
            ItemTableName = ItemTableName.Replace("{0}", value);
            this.UpdateCodeItem(text, ItemCodeHeaderID, ItemTableName, ItemColumnName);
        }


        fvParameter.ChangeMode(FormViewMode.Insert);

        Updating(this, new EventArgs());
    }

    //按下新增
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        this.LoadTableNames();

        //string tableName = ((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue;
        string tableName = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["TableName"];

        string value = ((TextBox)fvParameter.FindControl("txtValue")).Text;
        string text = ((TextBox)fvParameter.FindControl("txtText")).Text;
        string MOIValue = ((TextBox)fvParameter.FindControl("txtMOIValue")).Text;

        string columnName = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["ColumnName"];
        string itemColumnName = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["ItemColumnName"];
        string itemCodeHeaderID = fvParameter.FindDDLControl("ddlColumnName").SelectedItem.Attributes["ItemCodeHeaderID"];
        string itemTableName = string.Format(fvParameter.FindDDLControl("ddlColumnName").SelectedItem.Attributes["ItemTableName"], value);

        string codeHeaderID = ((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue;
        string tbName = ((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue
                + '_' + ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["ColumnName"]
                + '_' + ((TextBox)fvParameter.FindControl("txtValue")).Text;
        string IsGroup = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["IsGroup"];
       
        using (TransactionScope scope = new TransactionScope())
        {
            if (IsGroup  == "Y")
            {
                int parent = int.Parse(fvParameter.FindDDLControl("ddlColumnName").SelectedValue);

                InsertCodeItem(itemCodeHeaderID, itemTableName, itemColumnName, text, value, parent);
                InsertCodeMapping(tableName, columnName, value, text, MOIValue);
            }
            else {
                InsertCodeMapping(tableName, columnName, value, text, MOIValue);
            }
   
            scope.Complete();
        }
       


        ((TextBox)fvParameter.FindControl("txtValue")).Text = "";
        ((TextBox)fvParameter.FindControl("txtText")).Text = "";
        ((TextBox)fvParameter.FindControl("txtMOIValue")).Text = "";

        fvParameter.ChangeMode(FormViewMode.Insert);

        Inserting(this, new EventArgs());
 
    }


    protected void ddlColumnName_DataBound(object sender, EventArgs e)
    {
        CodeMappingDAO dao = new CodeMappingDAO();
        DataTable dt = dao.GetdllTableTextItem(((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue);

        this.LoadTableNames();

        //Session["ddlTableName"] = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["TableName"];
    }
    


    protected void LoadTableNames()
    {
        DropDownList ddlTableName = this.fvParameter.FindControl("ddlTableText") as DropDownList;
        DropDownList ddlColumnName = this.fvParameter.FindControl("ddlColumnName") as DropDownList;

        if (ddlTableName.Items.Count == 0 || ddlColumnName.Items.Count == 0)
        {
            return;
        }

        CodeMappingDAO dao = new CodeMappingDAO();
        DataTable dt = dao.GetdllTableTextItem(ddlTableName.SelectedValue);
        
        //clsData dat = new clsData();
        //MyStringBuilder sqlstr = new MyStringBuilder();
        //DataTable dtTable = new DataTable();

        //sqlstr.AppendFormat("select * from [CodeItem]");
        //dtTable = dat.GetData(sqlstr);

        foreach (ListItem li in ddlColumnName.Items)
        {
            if (li.Value == "")
            {
                continue;
            }

            dt.DefaultView.RowFilter = string.Format("ID = {0} ", li.Value);
            li.Attributes.Add("TableName", dt.DefaultView[0]["TableName"].ToString());
            li.Attributes.Add("ColumnName", dt.DefaultView[0]["ColumnName"].ToString());
            li.Attributes.Add("IsGroup", dt.DefaultView[0]["IsGroup"].ToString());
            li.Attributes.Add("ItemColumnName", dt.DefaultView[0]["ItemColumnName"].ToString());
            li.Attributes.Add("ItemCodeHeaderID", dt.DefaultView[0]["ItemCodeHeaderID"].ToString());
            li.Attributes.Add("ItemTableName", dt.DefaultView[0]["ItemTableName"].ToString());

            //dtTable.DefaultView.RowFilter = string.Format("ColumnName = '{0}' ", li.Value);
        }

    }

    protected void ddlTableText_DataBound(object sender, EventArgs e)
    {

        //Session["ddlCodeHeaderID"] = ((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue;

    }

    protected void ddlTableText_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Session["ddlCodeHeaderID"] = ((DropDownList)fvParameter.FindControl("ddlTableText")).SelectedValue;

        this.ObjectDataSource2.SelectParameters["CodeHeaderID"].DefaultValue = (this.fvParameter.FindControl("ddlTableText") as DropDownList).SelectedValue;
        (this.fvParameter.FindControl("ddlColumnName") as DropDownList).DataBind();
    }

    protected void ddlActive_DataBound(object sender, EventArgs e)
    {
        (sender as DropDownList).Items.Insert(0, MyObj.GetDDLPleaseChooseItem());
    }

    protected void ddlColumnName_SelectedIndexChanged(object sender, EventArgs e)
    {
        //Session["ddlTableName"] = ((DropDownList)fvParameter.FindControl("ddlColumnName")).SelectedItem.Attributes["TableName"];
    }



    public void InsertCodeItem(String CodeHeaderID,String TableName, String ColumnName, String Text, string Value, int parent)
    {


        clsData dat = new clsData();
        MyStringBuilder sqlstr = new MyStringBuilder();

        sqlstr.AppendFormat("INSERT INTO CodeItem (CodeHeaderID,TableName,ColumnName,ColumnText,AllowEdit,IsGroup, CodeValue, Parent,CreateDate,CreateBy) ");
        sqlstr.AppendFormat("SELECT ");
        sqlstr.AppendFormat("'{0}', ", CodeHeaderID);
        sqlstr.AppendFormat("'{0}', ", TableName);
        sqlstr.AppendFormat("'{0}', ", ColumnName);
        sqlstr.AppendFormat("'{0}', ", Text);
        sqlstr.AppendFormat("'{0}', ", 'Y');
        sqlstr.AppendFormat("'{0}',  ", 'N');
        sqlstr.AppendFormat("@CodeValue,");
        sqlstr.AppendFormat("@Parent,");
        sqlstr.AppendFormat(" {0}, ", "getdate()");
        sqlstr.AppendFormat(" '{0}' ", clsAuth.GetUserID());

        sqlstr.AddWithValue("CodeValue", Value);
        sqlstr.AddWithValue("Parent", parent);

        dat.AccData(sqlstr);
    }
    public void UpdateCodeMapping(String TableName, String ColumnName, String Value, String Text, String MOIValue)
    {
        clsData dat = new clsData();
        MyStringBuilder sqlstr = new MyStringBuilder();

        sqlstr.AppendFormat(" update codemapping ");
        sqlstr.AppendFormat(" set Text = '{0}' ", Text);
        sqlstr.AppendFormat(",MOIValue = '{0}' ", MOIValue);
        sqlstr.AppendFormat(",Active = '{0}' ", (this.fvParameter.FindControl("ddlActive") as DropDownList).SelectedValue);
        sqlstr.AppendFormat(" where ");
        sqlstr.AppendFormat(" TableName ='{0}' ", TableName);
        sqlstr.AppendFormat(" and ColumnName = '{0}' ", ColumnName);
        sqlstr.AppendFormat(" and Value='{0}' ;", Value);

        dat.AccData(sqlstr);

        Updating(this, new EventArgs());
    }
    public DataTable SelectCodeItem(String TableName, String ColumnName) 
    {
        clsData dat = new clsData();
        MyStringBuilder sql = new MyStringBuilder();

        sql.AppendFormat("Select ItemCodeHeaderID,ItemTableName,ItemColumnName From CodeItem ");
        sql.AppendFormat("Where  TableName='{0}' ",TableName);
        sql.AppendFormat("And ColumnName='{0}' ", ColumnName);
        return dat.GetData(sql.ToString());
   
    }

    public void UpdateCodeItem( String Text,String ItemCodeHeaderID,String ItemTableName,String ItemColumnName)
    {
        clsData dat = new clsData();
        MyStringBuilder sql = new MyStringBuilder();
    
        
        sql.AppendFormat("Update CodeItem Set ");
        sql.AppendFormat("ColumnText = '{0}' ",Text);
        sql.AppendFormat("Where CodeHeaderID ='{0}' ", ItemCodeHeaderID);
        sql.AppendFormat("And TableName='{0}' ", ItemTableName);
        sql.AppendFormat("And ColumnName='{0}' ", ItemColumnName);
       
        dat.AccData(sql);
    
    }

    public void InsertCodeMapping(String TableName, String ColumnName, String Value, String Text, String MOIValue)
    {
        clsData dat = new clsData();
        MyStringBuilder sqlstr = new MyStringBuilder();

        sqlstr.AppendFormat(" insert into codemapping(TableName,ColumnName,Value,Text,MOIValue,Sequence,CreateDate,CreateBy,Active) ");
        sqlstr.AppendFormat(" select ");
        sqlstr.AppendFormat(" '{0}', ", TableName);
        sqlstr.AppendFormat(" '{0}', ", ColumnName);
        sqlstr.AppendFormat(" '{0}', ", Value);
        sqlstr.AppendFormat(" '{0}', ", Text);
        sqlstr.AppendFormat(" '{0}', ", MOIValue);
        sqlstr.AppendFormat(" (select max(sequence)+1 from codemapping ");
        sqlstr.AppendFormat(" where TableName='{0}' ", TableName);
        sqlstr.AppendFormat(" and ColumnName='{0}'), ", ColumnName);
        sqlstr.AppendFormat(" {0}, ", "getdate()");
        sqlstr.AppendFormat(" '{0}' ", clsAuth.GetUserID());
        sqlstr.AppendFormat(",'{0}' ", (this.fvParameter.FindControl("ddlActive") as DropDownList).SelectedValue);
        dat.AccData(sqlstr);

    }

    //public void ChangeFormViewMode()
    //{
    //    fvParameter.ChangeMode(FormViewMode.Insert);
    //}

    public delegate void UpdatingHandler(object sender, EventArgs e);
    public event UpdatingHandler Updating;

    public delegate void InsertHandler(object sender, EventArgs e);
    public event InsertHandler Inserting;

}


